﻿using System;
using System.Data.SqlClient;
using System.Data;



namespace ExoticCarRental
{
    public class BusinessLayer
    {
        /// <summary>
        ///     Business Layer Constructor
        /// </summary>
        public BusinessLayer()
        {

        }
        /// <summary>
        /// Logins the customer into the site.
        /// </summary>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <returns>integer
        /// 0 for success, 
        /// 1 for failure login, 
        /// </returns>
        public int LoginCustomer(string email, string password)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prLoginCustomer";

            cmd.Parameters.AddWithValue("@Email", email);
            cmd.Parameters.AddWithValue("@Password", password);

            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return Int32.Parse(row["status"].ToString());

        }

        /// <summary>
        /// Logins the agent into the application.
        /// </summary>
        /// <param name="login"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public DataRow LoginAgent(string login, string password)
        {

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prLoginAgent";

            cmd.Parameters.AddWithValue("@Login", login);
            cmd.Parameters.AddWithValue("@Password", password);

            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return row;

        }

        /// <summary>
        /// Create Customer Login
        /// </summary>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <returns></returns>

        public int CreateLogin(string email, string password)
        {

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prCreateCustomerLogin";

            cmd.Parameters.AddWithValue("@Email", email);
            cmd.Parameters.AddWithValue("@Password", password);

            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return Int32.Parse(row["status"].ToString());

        }

        /// <summary>
        /// Creates Agent record
        /// </summary>
        /// <param name="agent_name"></param>
        /// <param name="login"></param>
        /// <param name="password"></param>
        /// <param name="date_hired"></param>
        /// <param name="location_id"></param>
        /// <param name="date_terminated"></param>
        /// <returns></returns>
        public int CreateAgent(string agent_name,
                               string login,
                               string password,
                               DateTime date_hired,
                               int location_id)
        {

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prCreateAgent";


            cmd.Parameters.AddWithValue("@agent_name", agent_name);
            cmd.Parameters.AddWithValue("@login", login);
            cmd.Parameters.AddWithValue("@password", password);
            cmd.Parameters.AddWithValue("@date_hired", date_hired);
            cmd.Parameters.AddWithValue("@location_id", location_id);




            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return Int32.Parse(row["status"].ToString());

        }

        /// <summary>
        /// Create Customer Records
        /// </summary>
        /// <param name="email_addr"></param>
        /// <param name="last_name"></param>
        /// <param name="first_name"></param>
        /// <param name="addr1"></param>
        /// <param name="addr2"></param>
        /// <param name="city"></param>
        /// <param name="state"></param>
        /// <param name="country"></param>
        /// <param name="postal_code"></param>
        /// <param name="license_no"></param>
        /// <param name="dob"></param>
        /// <param name="phone_no"></param>
        /// <returns>int 0 = success, 1 = failure</returns>

        public int CreateCustomer(string email_addr,
                       string last_name,
                       string first_name,
                       string addr1,
                       string addr2,
                       string city,
                       string state,
                       string country,
                       string postal_code,
                       string license_no,
                       DateTime dob,
                       string phone_no)
        {

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prCreateCustomer";


            cmd.Parameters.AddWithValue("@email_addr", email_addr);
            cmd.Parameters.AddWithValue("@last_name", last_name);
            cmd.Parameters.AddWithValue("@first_name", first_name);
            cmd.Parameters.AddWithValue("@addr1", addr1);
            cmd.Parameters.AddWithValue("@addr2", addr2);
            cmd.Parameters.AddWithValue("@city", city);
            cmd.Parameters.AddWithValue("@state", state);
            cmd.Parameters.AddWithValue("@country", country);
            cmd.Parameters.AddWithValue("@postal_code", postal_code);
            cmd.Parameters.AddWithValue("@license_no", license_no);
            cmd.Parameters.AddWithValue("@dob", dob);
            cmd.Parameters.AddWithValue("@phone_no", phone_no);




            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return Int32.Parse(row["cust_id"].ToString());

        }

        /// <summary>
        /// Create Customer CreditCard
        /// </summary>
        /// <param name="cust_id"></param>
        /// <param name="ccard_no"></param>
        /// <param name="ccard_type"></param>
        /// <param name="ccard_expmonth"></param>
        /// <param name="ccard_expyear"></param>
        /// <returns>int 0 = success, 1 = failure</returns>
        public int CreateCustomerCreditCard(string email_addr,
               string ccard_no,
               string ccard_type,
               int ccard_expmonth,
               int ccard_expyear
               )
        {


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prCreateCustomerCreditCard";


            cmd.Parameters.AddWithValue("@email_addr", email_addr);
            cmd.Parameters.AddWithValue("@ccard_no", ccard_no);
            cmd.Parameters.AddWithValue("@ccard_type", ccard_type);
            cmd.Parameters.AddWithValue("@ccard_expmonth", ccard_expmonth);
            cmd.Parameters.AddWithValue("@ccard_expyear", ccard_expyear);




            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return Int32.Parse(row["status"].ToString());

        }




        /// <summary>
        /// Create Location
        /// </summary>
        /// <param name="email_addr"></param>
        /// <param name="location_addr1"></param>
        /// <param name="location_addr2"></param>
        /// <param name="location_city"></param>
        /// <param name="location_state"></param>
        /// <param name="location_zip"></param>
        /// <param name="location_phoneno"></param>
        /// <returns>int returns number of rows affected </returns>

        public int CreateLocation(string email_addr,
                   string location_addr1,
                   string location_addr2,
                   string location_city,
                   string location_state,
                   string location_zip,
                   string location_phoneno
)
        {


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prCreateLocation";


            cmd.Parameters.AddWithValue("@location_addr1", location_addr1);
            cmd.Parameters.AddWithValue("@location_addr2", location_addr2);
            cmd.Parameters.AddWithValue("@location_city", location_city);
            cmd.Parameters.AddWithValue("@location_state", location_state);
            cmd.Parameters.AddWithValue("@location_zip", location_zip);
            cmd.Parameters.AddWithValue("@location_phoneno", location_phoneno);




            DataLayer dal = new DataLayer();
            int rowsaffected = dal.ExecuteProcNonQuery(cmd);

            return rowsaffected;

        }

        /// <summary>
        /// Creates Reservation
        /// </summary>
        /// <param name="email_addr"></param>
        /// <param name="last_name"></param>
        /// <param name="pickup_date"></param>
        /// <param name="dropoff_date"></param>
        /// <param name="pickup_locationid"></param>
        /// <param name="category"></param>
        /// <param name="ccard_no"></param>
        /// <param name="ccard_type"></param>
        /// <param name="ccard_expmonth"></param>
        /// <param name="ccard_expyear"></param>
        /// <returns>int reservation_id</returns>

        public int CreateReservation(string email_addr,
               string last_name,
               string first_name,
               DateTime pickup_date,
               DateTime dropoff_date,
               int pickup_locationid,
               int category,
               string ccard_no,
               string ccard_type,
               int ccard_expmonth,
               int @ccard_expyear
              )
        {


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prCreateReservation";


            cmd.Parameters.AddWithValue("@email_addr", email_addr);
            cmd.Parameters.AddWithValue("@last_name", last_name);
            cmd.Parameters.AddWithValue("@first_name", first_name);
            cmd.Parameters.AddWithValue("@pickup_date", pickup_date);
            cmd.Parameters.AddWithValue("@dropoff_date", dropoff_date);
            cmd.Parameters.AddWithValue("@pickup_locationid", pickup_locationid);
            cmd.Parameters.AddWithValue("@category", category);
            cmd.Parameters.AddWithValue("@ccard_no", ccard_no);
            cmd.Parameters.AddWithValue("@ccard_type", ccard_type);
            cmd.Parameters.AddWithValue("@ccard_expmonth", ccard_expmonth);
            cmd.Parameters.AddWithValue("@ccard_expyear", ccard_expyear);


            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return Int32.Parse(row["reservation_id"].ToString());

        }


        /// <summary>
        /// Create Car Category
        /// </summary>
        /// <param name="category_name"></param>
        /// <param name="category_desc"></param>
        /// <returns></returns>

        public int CreateCarCategory(string category_name,
           string category_desc

)
        {


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prCreateCarCategory";



            cmd.Parameters.AddWithValue("@category_name", category_name);
            cmd.Parameters.AddWithValue("@category_desc", category_desc);




            DataLayer dal = new DataLayer();
            int rowsaffected = dal.ExecuteProcNonQuery(cmd);

            return rowsaffected;

        }

        /// <summary>
        /// Create Car Type
        /// </summary>
        /// <param name="make"></param>
        /// <param name="model"></param>
        /// <param name="year"></param>
        /// <param name="category_id"></param>
        /// <returns>int rows affected</returns>
        public int CreateCarType(string make,
           string model,
            int year,
            int @category_id)
        {



            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prCreateCarType";



            cmd.Parameters.AddWithValue("@make", make);
            cmd.Parameters.AddWithValue("@model", model);
            cmd.Parameters.AddWithValue("@year", year);
            cmd.Parameters.AddWithValue("@category_id", category_id);




            DataLayer dal = new DataLayer();
            int rowsaffected = dal.ExecuteProcNonQuery(cmd);

            return rowsaffected;

        }

        /// <summary>
        /// Get Car Locatons LookUp
        /// </summary>
        /// <returns>
        ///     Returns a Datatale of Locations
        /// </returns>
        public DataTable GetLocations()
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetLocations";
            return dal.Execute(cmd);

        }
        /// <summary>
        ///     Get LOcations for AboutUs Web Page
        /// </summary>
        /// <returns>
        ///     Returns a Datatale of Locations
        /// </returns>
        public DataTable GetAboutUsLocations()
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetAboutUsLocations";
            return dal.Execute(cmd);

        }

        /// <summary>
        ///     Gets a list of states as Lookup
        /// </summary>
        /// <returns>
        ///     Returns a Data Table of States.
        /// </returns>
        public DataTable GetStates()
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetStates";
            return dal.Execute(cmd);

        }

        /// <summary>
        ///     Get list of categories
        /// </summary>
        /// <returns>
        ///     Returns a Data Table of categories.
        /// </returns>
        public DataTable GetCategories()
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetCategories";
            return dal.Execute(cmd);

        }

        /// <summary>
        ///     Get list of Countries
        /// </summary>
        /// <returns>
        ///     Returns a Data Table of Countries.
        /// </returns>
        public DataTable GetCountries()
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetCountries";
            return dal.Execute(cmd);

        }

        /// <summary>
        ///     
        /// </summary>
        /// <param name="email_addr" type="string">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="agent_id" type="int">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="reservation_id" type="int">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="category_id" type="int">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="ccard_id" type="int">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="rental_date" type="System.DateTime">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="return_date" type="System.DateTime">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="return_locationid" type="int">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="reg_no" type="string">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="license_no" type="string">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <param name="phone_no" type="string">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <returns>
        ///     Returns Integer Status 0 sucess and 1 Failure
        /// </returns>
        public int CreateRental(string email_addr, 
                 int agent_id,
                 int reservation_id,
                 int category_id,
                 int ccard_id,
                 DateTime rental_date,
                 DateTime return_date,
                 int return_locationid,
                 string reg_no,
                 string license_no,
                 string phone_no
            )
        {


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prCreateRentalContract";

            cmd.Parameters.AddWithValue("@email_addr", email_addr);
            cmd.Parameters.AddWithValue("@agent_id", agent_id);
            cmd.Parameters.AddWithValue("@reservation_id", reservation_id);
            cmd.Parameters.AddWithValue("@category_id", category_id);
            cmd.Parameters.AddWithValue("@ccard_id", ccard_id);
            cmd.Parameters.AddWithValue("@rental_date", rental_date);
            cmd.Parameters.AddWithValue("@return_date", return_date);
            cmd.Parameters.AddWithValue("@return_locationid", return_locationid);
            cmd.Parameters.AddWithValue("@reg_no", reg_no);
            cmd.Parameters.AddWithValue("@license_no", license_no);
            cmd.Parameters.AddWithValue("@phone_no", phone_no);



            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return Int32.Parse(row["status"].ToString());

        }
        /// <summary>
        ///     Get Reservations LookUps
        /// </summary>
        /// <returns>
        ///     Returns a table of Reservaions
        /// </returns>

        public DataTable GetReservationLookUp()
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetReservationLookUp";
            return dal.Execute(cmd);

        }

        /// <summary>
        ///     Get a List f Customers
        /// </summary>
        /// <returns>
        ///     Returns a DataTable of Customers
        /// </returns>
        public DataTable GetCustomerLookUp()
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetCustomerIDLookUp";
            return dal.Execute(cmd);

        }


        /// <summary>
        ///     Gets Reservation
        /// </summary>
        /// <param name="reservation_id" type="int">
        ///     <para>
        ///         Takes in ReservationID as Paramter
        ///     </para>
        /// </param>
        /// <returns>
        ///     Returns a DataRow of Reservations
        /// </returns>

        public DataRow GetReservationByReservationID(int reservation_id)
        {
            DataLayer dal = new DataLayer();


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetReservationByReservationID";
            cmd.Parameters.AddWithValue("@reservation_id", reservation_id);
            DataTable dt = dal.Execute(cmd);
            DataRow row = dt.Rows[0];
            return row;



        }

        /// <summary>
        ///     Get Reservation By LastName
        /// </summary>
        /// <param name="last_name" type="string">
        ///     <para>
        ///         Takes Last Name as Parameter
        ///     </para>
        /// </param>
        /// <returns>
        ///     Returns a DataRow of Reservations
        /// </returns>
        public DataRow GetReservationByLastName(string last_name)
        {
            DataLayer dal = new DataLayer();


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetReservationByLastName";
            cmd.Parameters.AddWithValue("@last_name", last_name);
            DataTable dt = dal.Execute(cmd);
            DataRow row = dt.Rows[0];
            return row;



        }

        /// <summary>
        ///     Get Reservation By FirstName
        /// </summary>
        /// <param name="first_name" type="string">
        ///     <para>
        ///         Takes First Name as Parameter
        ///     </para>
        /// </param>
        /// <returns>
        ///     Returns a DataRow of Reservations
        /// </returns>

        public DataRow GetReservationByFirstName(string first_name)
        {
            DataLayer dal = new DataLayer();


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetReservationByFirstName";
            cmd.Parameters.AddWithValue("@first_name", first_name);
            DataTable dt = dal.Execute(cmd);
            DataRow row = dt.Rows[0];
            return row;



        }

        /// <summary>
        ///     Get Reservation By CreditCardNUmber
        /// </summary>
        /// <param name="ccard_no" type="string">
        ///     <para>
        ///         Takes Reservation by Credit Card Number
        ///     </para>
        /// </param>
        /// <returns>
        ///     Returns a DataRow of Reservations
        /// </returns>
        public DataRow GetReservationByCreditCardNUmber(string ccard_no)
        {
            DataLayer dal = new DataLayer();


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetReservationByCreditCardNumber";
            cmd.Parameters.AddWithValue("@ccard_no", ccard_no);
            DataTable dt = dal.Execute(cmd);
            DataRow row = dt.Rows[0];
            return row;



        }
        /// <summary>
        ///     Get Customer Reservations
        /// </summary>
        /// <param name="email" type="string">
        ///     <para>
        ///          Take Email as Paremeter
        ///     </para>
        /// </param>
        /// <returns>
        ///     Returns a DataTable of Reservations
        /// </returns>
        public DataTable GetMyReservations(string email)
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@email_addr", email);
            cmd.CommandText = "prGetMyReservations";
            return dal.Execute(cmd);


        
        }


        /// <summary>
        ///     
        /// </summary>
        /// <param name="category_id" type="int">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <returns>
        ///     A System.Data.DataRow value...
        /// </returns>
        public DataRow GetCategory(int category_id)
        {
            DataLayer dal = new DataLayer();


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetCategory";
            cmd.Parameters.AddWithValue("@category_id", category_id);
            DataTable dt = dal.Execute(cmd);
            DataRow row = dt.Rows[0];
            return row;



        }

        public int DeleteReservation(int reservation_id)
        {


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prDeleteReservation";


            cmd.Parameters.AddWithValue("@reservation_id", reservation_id);
            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return Int32.Parse(row["status"].ToString());

        }

        public int TerminateAgent(string login)
        {


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prTerminateAgent";


            cmd.Parameters.AddWithValue("@login", login);
            DataLayer dal = new DataLayer();
            DataTable dt = dal.Execute(cmd);

            DataRow row = dt.Rows[0];
            return Int32.Parse(row["status"].ToString());

        }

        public DataTable GetAgents()
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetAgents";
            return dal.Execute(cmd);

        }


        public DataRow GetAgent(string login)
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetAgent";
            cmd.Parameters.AddWithValue("@login", login);
            DataTable dt = dal.Execute(cmd);
            return dt.Rows[0];

        }

        public DataTable GetCustomerCards(string email_addr)
        {
            DataLayer dal = new DataLayer();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetCustomerCards";
            cmd.Parameters.AddWithValue("@email_addr", email_addr);
            return dal.Execute(cmd);

        }


        public DataRow GetReservation(int reservation_id)
        {
            DataLayer dal = new DataLayer();


            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "prGetReservation";
            cmd.Parameters.AddWithValue("@reservation_id", reservation_id);
            DataTable dt = dal.Execute(cmd);
            DataRow row = dt.Rows[0];
            return row;



        }


    }




   



}
